﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using ParsePtb.attr;
using ParsePtb.serivce;
using ParsePtb.serivce.impl;

namespace ParsePtb.util
{
    public static class SQLiteSqlUtils
    {

        /// <summary>
        /// 创建 查询语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t">对象</param>
        /// <param name="whereColums">查询出的字段,如[id,name]</param>
        /// <returns></returns>
        public static string CreateSelectSql<T>(T t, string[] whereColums)
        {
            return CreateSelectSql<T>(t, whereColums, null, null);
        }


        /// <summary>
        /// 创建 查询语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t">对象</param>
        /// <param name="whereColums">查询出的字段,如[id,name]</param>
        /// <param name="joinSql">关联语句</param>
        /// <param name="joinWhereCon">关联表的查询条件【不需要待where 但需要带and】</param>
        /// <returns></returns>
        public static string CreateSelectSql<T>(T t, string[] whereColums, string joinSql, string joinWhereCon)
        {
            Type type = typeof(T);

            joinSql = joinSql == null ? "" : joinSql;
            joinWhereCon = joinWhereCon == null ? "" : joinWhereCon;

            BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
            var startTime = DateTime.Now;
            TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
            string tableName = tableAttr != null ? tableAttr.Value : "";

            string sqlTemp = "SELECT #{colums} FROM " + tableName + " \n " + joinSql + " WHERE 1=1 #{whereColums} " + joinWhereCon;

            Dictionary<string, TableFieldAttr> fieldAttrDic = new Dictionary<string, TableFieldAttr>();

            StringBuilder colums = new StringBuilder();

            StringBuilder whereColumsTem = new StringBuilder("");

            FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

            PropertyInfo propertyInfo = null;

            int countFlag = 0;
            IEnumerable<TableFieldAttr> enumerable = null;

            for (int i = 0; i < fieldInfos.Length; i++)
            {
                enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

                foreach (TableFieldAttr attr in enumerable)
                {
                    //不是主键 和是数据库字段
                    if (attr.IsExist)
                    {
                        fieldAttrDic.Add(fieldInfos[i].Name, attr);
                        //加上表名
                        colums.Append((countFlag == 0 ? "" : ",") + tableName + "." + attr.ColumName);
                        countFlag++;
                    }
                }

            }

            countFlag = 0;
            TableFieldAttr fieldAttr = null;
            if (whereColums != null)
            {
                for (int k = 0; k < whereColums.Length; k++)
                {
                    propertyInfo = type.GetProperty(UpperCaseFirst(whereColums[k]), bindingFlags);
                    if (propertyInfo != null)
                    {
                        fieldAttr = fieldAttrDic[whereColums[k]];
                        whereColumsTem.Append(" and " + (fieldAttr == null ? "NoField" : tableName + "." + fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'");
                        /*whereColumsTem.Append((countFlag == 0 ? " " : " and ") + tableName + "." + (fieldAttr == null ? "NoField" : fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'");
                        countFlag++;*/
                    }

                }
            }


            string selectSql = sqlTemp.Replace("#{colums}", colums.ToString()).Replace("#{whereColums}", whereColumsTem.ToString());

            return selectSql;
        }


        /// <summary>
        /// 创建 删除语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t">对象</param>
        /// <param name="whereColums">查询出的字段,如[id,name]</param>
        /// <returns></returns>
        public static string CreateDeleteSql<T>(T t, string[] whereColums)
        {
            Dictionary<string, TableFieldAttr> fieldAttrDic = new Dictionary<string, TableFieldAttr>();
            Type type = typeof(T);
            BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
            var startTime = DateTime.Now;
            TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
            string tableName = tableAttr != null ? tableAttr.Value : "";

            string sqlTemp = "DELETE FROM " + tableName + " WHERE 1=1 #{whereColums}";

            StringBuilder whereColumsTem = new StringBuilder("");

            FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

            PropertyInfo propertyInfo = null;

            IEnumerable<TableFieldAttr> enumerable = null;

            // int countFlag = 0;

            for (int i = 0; i < fieldInfos.Length; i++)
            {
                enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

                foreach (TableFieldAttr attr in enumerable)
                {
                    //不是主键 和是数据库字段
                    if (attr.IsExist)
                    {
                        fieldAttrDic.Add(fieldInfos[i].Name, attr);
                    }
                }
            }

            TableFieldAttr fieldAttr = null;
            if (whereColums != null)
            {
                for (int k = 0; k < whereColums.Length; k++)
                {
                    propertyInfo = type.GetProperty(UpperCaseFirst(whereColums[k]), bindingFlags);
                    if (propertyInfo != null)
                    {
                        fieldAttr = fieldAttrDic[whereColums[k]];
                        whereColumsTem.Append(" and " + (fieldAttr == null ? "NoField" : fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'");
                    }

                }
            }

            string deleteSql = sqlTemp.Replace("#{whereColums}", whereColumsTem.ToString());
            return deleteSql;
        }

        public static string CreateInsertSql<T>(T t)
        {
            List<T> list = new List<T>();
            list.Add(t);
            return CreateInsertSql<T>(list, new ColumServiceImpl());

        }

        public static string CreateInsertSql<T>(T t, ColumService columService)
        {
            List<T> list = new List<T>();
            list.Add(t);
            return CreateInsertSql<T>(list, columService);

        }

        public static string CreateInsertSql<T>(List<T> list)
        {
            return CreateInsertSql<T>(list, new ColumServiceImpl());

        }

        //新增 批量新增的sql
        public static string CreateInsertSql<T>(List<T> list, ColumService columService)
        {
            Type type = typeof(T);
            BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
            var startTime = DateTime.Now;
            TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
            string tableName = tableAttr != null ? tableAttr.Value : "";


            string insertHead = "INSERT INTO " + tableName + "(#{colums}) VALUES\n";
            string valuesTemp = "(#{values})";


            FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

            Dictionary<string, TableFieldAttr> table = new Dictionary<string, TableFieldAttr>();

            Dictionary<string, PropertyInfo> tablePropertys = new Dictionary<string, PropertyInfo>();

            //string iteminfo = "";
            PropertyInfo propertyinfo = null;

            TableFieldAttr tableFieldAttr = null;

            IEnumerable<TableFieldAttr> enumerable = null;

            string columsTemplet = "";
            string valuesTemplet = "";

            for (int i = 0; i < fieldInfos.Length; i++)
            {
                tablePropertys.Add(fieldInfos[i].Name, type.GetProperty(UpperCaseFirst(fieldInfos[i].Name), bindingFlags));
                enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

                foreach (TableFieldAttr attr in enumerable)
                {
                    table.Add(fieldInfos[i].Name, attr);
                    attr.JdbcType = fieldInfos[i].FieldType.ToString();
                    //不是主键 和是数据库字段
                    if (attr.IsExist)
                    {
                        columsTemplet += " " + attr.ColumName + " ,";
                        valuesTemplet += " #{" + fieldInfos[i].Name + "} ,";
                    }
                }
            }

            insertHead = insertHead.Replace("#{colums}", columsTemplet.Substring(0, columsTemplet.Length - 1));

            valuesTemp = valuesTemp.Replace("#{values}", valuesTemplet.Substring(0, valuesTemplet.Length - 1));

            string valuesInfo = "";
            string setValue = "";
            for (int k = 0; k < list.Count; k++)
            {

                object value = "";
                string currentValue = "";
                string rowInfo = valuesTemp;
                //rowInfo = ;
                foreach (string key in table.Keys)
                {
                    propertyinfo = tablePropertys[key];

                    tableFieldAttr = table[key];
                    //默认值 不进行函数转换
                    if (tableFieldAttr.Value != null)
                    {
                        value = tableFieldAttr.Value;
                        currentValue = value.ToString();
                        setValue = currentValue;
                    }
                    else if (propertyinfo != null)
                    {
                        value = tablePropertys[key].GetValue(list[k], null);
                        currentValue = (value == null) ? "''" : "'" + value.ToString() + "'";
                        setValue = tableFieldAttr.DefinedFunc ? columService.DoFormat(key, currentValue, tableFieldAttr.PatternStr) : currentValue;
                    }


                    rowInfo = rowInfo.Replace("#{" + key + "}", setValue);
                }

                valuesInfo += rowInfo + ",";

            }

            var endTime = DateTime.Now;
            TimeSpan ts = endTime.Subtract(startTime);
            Console.WriteLine("insert语句生成耗时{0}ms.", ts.TotalMilliseconds);

            return insertHead + valuesInfo.Substring(0, valuesInfo.Length - 1);
        }

        /// <summary>
        ///  单条数据更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="whereColums"></param>
        /// <returns></returns>
        public static string CreateUpdateSql<T>(T t, string[] whereColums)
        {
            List<T> list = new List<T>();
            list.Add(t);
            return CreateUpdateSql<T>(list, whereColums, null, new ColumServiceImpl());
        }


        /// <summary>
        ///  更新的语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="whereColums">whereColums 必填  参考 新增和修改【id,name】</param>
        /// <param name="columService"></param>
        /// <returns></returns>
        public static string CreateUpdateSql<T>(T t, string[] whereColums, ColumService columService)
        {
            List<T> list = new List<T>();
            list.Add(t);
            return CreateUpdateSql<T>(list, whereColums, null, columService);
        }

        /// <summary>
        /// 多条数据更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="whereColums"></param>
        /// <returns></returns>
        public static string CreateUpdateSql<T>(List<T> list, string[] whereColums)
        {
            return CreateUpdateSql<T>(list, whereColums, null, new ColumServiceImpl());
        }

        /// <summary>
        /// 选择性更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="whereColums"></param>
        /// <param name="selectColumns">选择性的字段</param>
        /// <param name="columService"></param>
        /// <returns></returns>
        public static string CreateSelectUpdateSql<T>(T t, string[] selectColumns, string[] whereColums)
        {
            List<T> list = new List<T>();
            list.Add(t);
            return CreateUpdateSql<T>(list, whereColums, selectColumns, null);
        }

        /// <summary>
        /// 多条数据选择性更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="selectColumns"></param>
        /// <param name="whereColums"></param>
        /// <returns></returns>
        public static string CreateSelectUpdateSql<T>(List<T> list, string[] selectColumns, string[] whereColums)
        {
            return CreateUpdateSql<T>(list, whereColums, selectColumns, null);
        }


        /// <summary>
        /// 修改 批量修改的sql  主语法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="whereColums"></param>
        /// <param name="columService"></param>
        /// <returns></returns>
        public static string CreateUpdateSql<T>(List<T> list, string[] whereColums, string[] selectColums, ColumService columService)
        {
            StringBuilder whereColumsTem = new StringBuilder("");
            List<string> whereColumTs = new List<string>();
            //选择性更新
            List<string> selectColumTs = new List<string>();

            if (whereColums != null)
            {
                whereColumTs = new List<string>(whereColums);
            }

            if (selectColums != null)
            {
                selectColumTs = new List<string>(selectColums);
            }

            var startTime = DateTime.Now;
            BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
            Type type = typeof(T);

            TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>();
            string tableName = tableAttr != null ? tableAttr.Value : "";

            string updateSql = "";

            string updateHead = "UPDATE " + tableName + " #{colums} ";


            FieldInfo[] fieldInfos = type.GetFields(bindingFlags);

            Dictionary<string, TableFieldAttr> table = new Dictionary<string, TableFieldAttr>();

            Dictionary<string, PropertyInfo> tablePropertys = new Dictionary<string, PropertyInfo>();

            PropertyInfo propertyinfo = null;

            TableFieldAttr tableFieldAttr = null;
            IEnumerable<TableFieldAttr> enumerable = null;



            string setTemplet = "";
            for (int i = 0; i < fieldInfos.Length; i++)
            {
                tablePropertys.Add(fieldInfos[i].Name, type.GetProperty(UpperCaseFirst(fieldInfos[i].Name), bindingFlags));
                enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>();

                foreach (TableFieldAttr attr in enumerable)
                {
                    table.Add(fieldInfos[i].Name, attr);
                    //选择性更新  不匹配的字段进行过滤
                    if (selectColumTs.Count > 0 && !selectColumTs.Contains(fieldInfos[i].Name))
                    {
                        attr.IsUpdate = false;
                    }

                    attr.JdbcType = fieldInfos[i].FieldType.ToString();
                    //不是主键 和是数据库字段
                    if (attr.IsExist && !attr.PrimaryKey && attr.IsUpdate)
                    {
                        setTemplet += " " + attr.ColumName + "= #{" + fieldInfos[i].Name + "} ,";
                    }

                    //匹配
                    if (whereColumTs.Contains(fieldInfos[i].Name))
                    {
                        whereColumsTem.Append(" AND " + attr.ColumName + " = #{" + fieldInfos[i].Name + "}");
                    }

                }
            }
            setTemplet = "set " + setTemplet.Substring(0, setTemplet.Length - 1);



            if (whereColumTs.Count > 0)
            {
                setTemplet += " WHERE 1=1 " + whereColumsTem.ToString();
            }

            //updateHead += setTemplet;

            string colum = "";
            object value = "";
            string currentValue = "";
            for (int k = 0; k < list.Count; k++)
            {
                colum = setTemplet;

                foreach (string key in table.Keys)
                {
                    propertyinfo = tablePropertys[key];

                    tableFieldAttr = table[key];

                    if (tableFieldAttr.Value != null)
                    {
                        value = tableFieldAttr.Value;
                        //默认值不做函数处理
                        currentValue = value.ToString();
                    }
                    else if (propertyinfo != null)
                    {
                        value = tablePropertys[key].GetValue(list[k], null);
                        currentValue = (value == null) ? "''" : "'" + value.ToString() + "'";
                        //主键在更新语句 不自动生成
                        if (!tableFieldAttr.PrimaryKey)
                        {
                            //实际值才会做函数处理
                            currentValue = tableFieldAttr.DefinedFunc ? columService.DoFormat(key, currentValue, tableFieldAttr.PatternStr) : currentValue;
                        }
                    }
                    else
                    {
                        currentValue = "''";
                    }
                    colum = colum.Replace("#{" + key + "}", currentValue);

                }
                updateSql += updateHead.Replace("#{colums}", colum) + "\n;";
            }
            updateSql = updateSql.Substring(0, updateSql.Length - 1);

            var endTime = DateTime.Now;
            TimeSpan ts = endTime.Subtract(startTime);
            Console.WriteLine("update语句生成耗时{0}ms.", ts.TotalMilliseconds);

            return updateSql;
        }

        /// <summary>
        ///  首字母大写
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string UpperCaseFirst(this string str)
        {
            if (string.IsNullOrWhiteSpace(str))
                return string.Empty;
            char[] s = str.ToCharArray();
            char c = s[0];
            if ('a' <= c && c <= 'z')
                c = (char)(c & ~0x20);
            s[0] = c;

            return new string(s);
        }
    }
}
